package com.spstudio.iforward.end.impl;

import java.security.Principal;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.logging.Logger;

import javax.ejb.Stateless;
import javax.inject.Inject;

import com.spstudio.iforward.core.helper.DatabaseHelper;
import com.spstudio.iforward.core.qualifier.SPCoreLogged;
import com.spstudio.iforward.end.interfaces.IStatisticsBeanRemote;

/**
 * Session Bean implementation class StatisticsStatelessBean
 */
@Stateless
public class StatisticsStatelessBean implements IStatisticsBeanRemote {

	private static final int QUERY_LAST_DAY = 7;
	
	@Inject DatabaseHelper helper;
	@Inject Principal principal;
	@Inject @SPCoreLogged Logger log;
	
	/*
	 * QUERY CANDIDATE STORY
	 */
	private static final String QUERY_CONTRIBUTION_BY_USER = "SELECT u.username, count(sc.id) mycount FROM story_candidate sc right outer join users u ON sc.create_user_id = u.id group by u.id order by mycount desc";
	private static final String QUERY_CATEGORY_COUNT = "select category_name, story_sum from story_category order by story_sum desc";
	private static final String QUERY_LAST_7_DAY_STORY_PUBLISH_COUNT = "select publish_date, count(0) c from story where publish_date < sysdate() and publish_date > date_sub(sysdate(),INTERVAL " + QUERY_LAST_DAY + " DAY) group by publish_date order by publish_date desc";
	private static final String QUERY_SPECIAL_MONTH_STORY_PUBLISH_COUNT = "select publish_date, count(0) c from story where publish_date >= ? and publish_date < ? group by publish_date order by publish_date desc";

	@Override
	public List<String[]> retrieveContributionByUser() {
		List<Object[]> result = helper.doQuery(QUERY_CONTRIBUTION_BY_USER, null);
		List<String[]> returnList = new ArrayList<String[]>();
		if (result != null && result.size() != 0){
			for (Object[] data: result){
				returnList.add(new String[]{(String)data[0], String.valueOf(data[1])});
			}
		}
		return returnList;
	}

	@Override
	public List<String[]> retrieveCategoryCount() {
		List<Object[]> result = helper.doQuery(QUERY_CATEGORY_COUNT, null);
		List<String[]> returnList = new ArrayList<String[]>();
		if (result != null && result.size() != 0){
			for (Object[] data: result){
				returnList.add(new String[]{(String)data[0], String.valueOf(data[1])});
			}
		}
		return returnList;
	}

	@Override
	public List<String[]> retrieveLastSevenDayPublishStoryCount() {
		List<Object[]> result = helper.doQuery(QUERY_LAST_7_DAY_STORY_PUBLISH_COUNT, null);
		List<String[]> returnList = new ArrayList<String[]>();
		if (result != null && result.size() != 0){
			for (Object[] data: result){
				// Timestamp.toString() = yyyy-mm-dd hh:mm:ss.fffffffff
				returnList.add(new String[]{((Timestamp)data[0]).toString().substring(0, 10), String.valueOf(data[1])});
			}
		}

		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		for (int i = 0;i < QUERY_LAST_DAY; i ++){
			if (returnList.size() < i + 1){
				returnList.add(new String[]{"", "0"});
			}
			Calendar c = Calendar.getInstance();
			c.add(Calendar.DATE, -1 * i);
			if (!returnList.get(i)[0].equals(format.format(c.getTime()))){
				returnList.add(i, new String[]{format.format(c.getTime()), "0"});
			}
		}

		while (returnList.size() > QUERY_LAST_DAY){
			returnList.remove(QUERY_LAST_DAY);
		}
		return returnList;
	}

	@Override
	public List<String[]> retrieveSpecialMonthPublishStoryCount(Date date) {
		Calendar begin = Calendar.getInstance();
		begin.setTime(date);
		begin.set(Calendar.DAY_OF_MONTH, 1);
		begin.set(Calendar.HOUR_OF_DAY, 0);
		begin.set(Calendar.MINUTE, 0);
		begin.set(Calendar.SECOND, 0);
		
		Calendar end = Calendar.getInstance();
		end.setTime(date);
		int maxDays = end.getActualMaximum(Calendar.DATE);
		end.set(Calendar.DAY_OF_MONTH, maxDays);
		end.add(Calendar.DATE, 1);
		end.set(Calendar.HOUR_OF_DAY, 0);
		end.set(Calendar.MINUTE, 0);
		end.set(Calendar.SECOND, 0);
		
		List<Object[]> result = helper.doQuery(QUERY_SPECIAL_MONTH_STORY_PUBLISH_COUNT, new Object[]{new Timestamp(begin.getTimeInMillis()), new Timestamp(end.getTimeInMillis())});
		
		List<String[]> returnList = new ArrayList<String[]>();
		if (result != null && result.size() != 0){
			for (Object[] data: result){
				// Timestamp.toString() = yyyy-mm-dd hh:mm:ss.fffffffff
				returnList.add(new String[]{((Timestamp)data[0]).toString().substring(0, 10), String.valueOf(data[1])});
			}
		}
		
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		Calendar c = Calendar.getInstance();
		c.setTime(date);
		List<String[]> resultList = new ArrayList<String[]>();
		for (int i = 0;i < maxDays; i ++){
			c.set(Calendar.DAY_OF_MONTH, i + 1);
			String key = format.format(c.getTime());
			boolean hasFound = false;
			for (String[] row: returnList){
				if (key.equals(row[0])){
					resultList.add(new String[]{row[0], row[1]});
					hasFound = true;
					break;
				}
			}
			if (!hasFound){
				resultList.add(new String[]{key, "0"});
			}
		}
		
		return resultList;
	}

//	public static void main(String[] args){
//		Date d = new Date();
//		d.setMonth(1);
//		new StatisticsStatelessBean().retrieveSpecialMonthPublishStoryCount(d);
//	}
}
